package com.st.emps.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

public class JDBCUtil {

	// 填写jdbc.properties地址（全包名.jdbc）
	private static ResourceBundle rb = ResourceBundle.getBundle("com.st.emps.util.jdbc");

	private static String className = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String user = rb.getString("user");
	private static String password = rb.getString("password");

	static {

		// 加载驱动：Class.forName("");
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*
	 * 获得链接对象
	 * 
	 * @return Connection
	 * 
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}

	/*
	 * 关闭连接
	 * 
	 * @param connection
	 * 
	 * @param statement
	 * 
	 */
	public static void close(Connection connection, Statement statement) {
		close(connection, statement, null);
	}

	/*
	 * @param conn
	 * 
	 * @param st
	 * 
	 * @param rs
	 * 
	 */

	public static void close(Connection conn, Statement st, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (st != null) {
				st.close();
				st = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) throws SQLException {
		Connection con = JDBCUtil.getConnection();
		System.out.println(con);
		JDBCUtil.close(con, null, null);
	}

	// 不允许构造
	private JDBCUtil() {
	}

	public static int update(String sql, Object... obj) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i + 1, obj[i]);
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 9999;
	}

	/*
	 * @Param <T>
	 * 
	 * @Param sql //sql语句
	 * 
	 * @Param vals //查询条件的值,即传递的？值
	 * 
	 * @Param clazz //实体类的类型，此处需要将泛型传入
	 * 
	 * @Param fields //映射关系 key是实体类的属性名，value是sql语句查询出来的字段名
	 * 
	 * @return
	 */
	public static <T> List<T> query(StringBuffer sql, List<Object> vals, Class<T> clazz, Map<String, String> fields) {

		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<>();
		try {
			con = getConnection();
			ps = con.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				T obj = clazz.newInstance();
				for (Entry<String, String> map : fields.entrySet()) {

					String key = map.getKey();
					String val = map.getValue();
					try {
						Field field = clazz.getDeclaredField(key);
						field.setAccessible(true);
						field.set(obj, rs.getObject(val));
					} catch (NoSuchFieldException | SecurityException e) {
						e.printStackTrace();
					}
					

				}
				result.add(obj);
			}
		} catch (SQLException |InstantiationException |IllegalAccessException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps, rs);
		}
		return result;
	}

	public static Integer queryInt(StringBuffer sql, List<Object> vals) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
			return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, ps, rs);
		}
		return null;
	}

}
